﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CNotebook.Inventory;
namespace CNotebook.Sales
{
    public class ProductReturnManager:ConnectionManager
    {

        #region Others

        public bool IsDocumentExists(string _DOCUMENT_NUM)
        {
            DataSet ds = new DataSet();

            SqlCommand oCommand = new SqlCommand();
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM ORIN WHERE DOCNUM = @DOCNUM";
            oCommand.Parameters.Add(new SqlParameter("@DOCNUM", _DOCUMENT_NUM));
            SqlDataAdapter _ADAPTER = new SqlDataAdapter();

            _ADAPTER.SelectCommand = oCommand;
            _ADAPTER.Fill(ds);

            int iCount = ds.Tables[0].Rows.Count;

            if (iCount > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        #endregion

        #region Adding/Updating Invoice
        public void AddInvoice(ProductReturnUnit oUnit)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "INSERT INTO ORIN (DocNum, DocStatus, PostingDate, DueDate, DocDate, Location, StoreCode, CardCode, CardName, ContactPerson, PaymentTerms, Reference, CustRefPO, BillTo, ShipTo, SalesMan, Remarks, VATCode, VATRate, IsVATInclusive, GrossAmount, VATAmount, WithHoldingTax, Discount1, Discount2, Discount3, Discount4, TotalDiscount, NetAmount, Division, DivReference, DateCreated, CreatedBy, DateModified, ModifiedBy) VALUES " +
                                                        " (@DocNum, @DocStatus, @PostingDate, @DueDate, @DocDate, @Location, @StoreCode, @CardCode, @CardName, @ContactPerson, @PaymentTerms, @Reference, @CustRefPO, @BillTo, @ShipTo, @SalesMan, @Remarks, @VATCode, @VATRate, @IsVATInclusive, @GrossAmount, @VATAmount, @WithHoldingTax, @Discount1, @Discount2, @Discount3, @Discount4, @TotalDiscount, @NetAmount, @Division, @DivReference, GetDate(), @CreatedBy, GetDate(), @ModifiedBy)";

            oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
            oCommand.Parameters.Add(new SqlParameter("@DocStatus", oUnit.DocumentStatus));
            oCommand.Parameters.Add(new SqlParameter("@PostingDate", oUnit.PostingDate));
            oCommand.Parameters.Add(new SqlParameter("@DueDate", oUnit.DueDate));
            oCommand.Parameters.Add(new SqlParameter("@DocDate", oUnit.DocDate));

            oCommand.Parameters.Add(new SqlParameter("@Location", oUnit.Location));
            oCommand.Parameters.Add(new SqlParameter("@StoreCode", oUnit.StoreCode));
            oCommand.Parameters.Add(new SqlParameter("@CardCode", oUnit.CardCode));
            oCommand.Parameters.Add(new SqlParameter("@CardName", oUnit.CardName));
            oCommand.Parameters.Add(new SqlParameter("@ContactPerson", oUnit.ContactPerson));
            oCommand.Parameters.Add(new SqlParameter("@PaymentTerms", oUnit.PaymentTerms));
            oCommand.Parameters.Add(new SqlParameter("@Reference", oUnit.Reference));
            oCommand.Parameters.Add(new SqlParameter("@CustRefPO", oUnit.CustRefPO));
            oCommand.Parameters.Add(new SqlParameter("@BillTo", oUnit.BillTo));
            oCommand.Parameters.Add(new SqlParameter("@ShipTo", oUnit.ShipTo));
            oCommand.Parameters.Add(new SqlParameter("@SalesMan", oUnit.SalesMan));
            oCommand.Parameters.Add(new SqlParameter("@Remarks", oUnit.Remarks));
            oCommand.Parameters.Add(new SqlParameter("@VATCode", oUnit.TaxCode));
            oCommand.Parameters.Add(new SqlParameter("@VATRate", oUnit.TaxRate));
            oCommand.Parameters.Add(new SqlParameter("@IsVATInclusive", oUnit.IsVATInclusive));

            oCommand.Parameters.Add(new SqlParameter("@GrossAmount", oUnit.GrossAmnt));
            oCommand.Parameters.Add(new SqlParameter("@VATAmount", oUnit.VATAmt));
            oCommand.Parameters.Add(new SqlParameter("@WithHoldingTax", oUnit.WithHoldingTax));
            oCommand.Parameters.Add(new SqlParameter("@Discount1", oUnit.Discount1));
            oCommand.Parameters.Add(new SqlParameter("@Discount2", oUnit.Discount2));
            oCommand.Parameters.Add(new SqlParameter("@Discount3", oUnit.Discount3));
            oCommand.Parameters.Add(new SqlParameter("@Discount4", oUnit.Discount4));
            oCommand.Parameters.Add(new SqlParameter("@TotalDiscount", oUnit.TotalDiscount));
            oCommand.Parameters.Add(new SqlParameter("@NetAmount", oUnit.NetAmount));
            oCommand.Parameters.Add(new SqlParameter("@Division", oUnit.Division));
            oCommand.Parameters.Add(new SqlParameter("@DivReference", oUnit.DivReference));

            oCommand.Parameters.Add(new SqlParameter("@CreatedBy", oUnit.CreatedBy));
            oCommand.Parameters.Add(new SqlParameter("@ModifiedBy", oUnit.ModifiedBy));



            oCommand.ExecuteNonQuery();

            #region Line Items
            foreach (DataRow oRow in oUnit.LineItems.Rows)
            {
                if (oRow.RowState != DataRowState.Deleted)
                {
                    oCommand = new SqlCommand();
                    oCommand.Connection = this.Connection;

                    oCommand.CommandText = "INSERT INTO RIN1 (DocNum, ItemCode, ItemDesc, UoM, QtyOrder, CQtyOrder, Qty, CQty, PriceListCode, UnitPrice, IsFree, DiscPrcnt, Discount, TotalLineDisc, Amount, ItemGuID) VALUES " +
                                                                    "(@DocNum, @ItemCode, @ItemDesc, @UoM, @QtyOrder, @CQtyOrder, @Qty, @CQty, @PriceListCode, @UnitPrice, @IsFree, @DiscPrcnt, @Discount, @TotalLineDisc, @Amount, @ItemGuID)";



                    oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                    oCommand.Parameters.Add(new SqlParameter("@ItemCode", oRow["ItemCode"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@ItemDesc", oRow["ItemDesc"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@UoM", oRow["UoM"].ToString()));

                    if (oRow["QtyOrder"].ToString() == "")
                    {
                        oRow["QtyOrder"] = "0";
                    }
                    oCommand.Parameters.Add(new SqlParameter("@QtyOrder", Convert.ToDouble(oRow["QtyOrder"].ToString())));
                    oCommand.Parameters.Add(new SqlParameter("@CQtyOrder", Convert.ToDouble(oRow["CQtyOrder"].ToString())));
                    oCommand.Parameters.Add(new SqlParameter("@Qty", Convert.ToDouble(oRow["Qty"].ToString())));
                    oCommand.Parameters.Add(new SqlParameter("@CQty", Convert.ToDouble(oRow["CQty"].ToString())));
                    oCommand.Parameters.Add(new SqlParameter("@PriceListCode", oRow["PriceListCode"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@UnitPrice", Convert.ToDouble(oRow["UnitPrice"].ToString())));

                    if (oRow["IsFree"] != DBNull.Value)
                    {
                        oCommand.Parameters.Add(new SqlParameter("@IsFree", Convert.ToBoolean(oRow["IsFree"])));
                    }
                    else
                    {
                        oCommand.Parameters.Add(new SqlParameter("@IsFree", false));
                    }

                    oCommand.Parameters.Add(new SqlParameter("@DiscPrcnt", oRow["DiscPrcnt"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@Discount", Convert.ToDouble(oRow["Discount"].ToString())));
                    oCommand.Parameters.Add(new SqlParameter("@TotalLineDisc", oRow["TotalLineDisc"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@Amount", oRow["Amount"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@ItemGuID", oRow["ItemGuID"].ToString()));

                    oCommand.ExecuteNonQuery();
                }

            }
            #endregion

            //_table.Columns.Add("ItemCode", typeof(string));
            //_table.Columns.Add("ItemName", typeof(string));
            //_table.Columns.Add("QtySrvd", typeof(string));
            //_table.Columns.Add("UoM", typeof(string));
            //_table.Columns.Add("LotNumber", typeof(string));

            #region Inventory Issued Items
            foreach (DataRow oRow in oUnit.InventoryIssuedItems.Rows)
            {
                if (oRow.RowState != DataRowState.Deleted)
                {
                    oCommand = new SqlCommand();
                    oCommand.Connection = this.Connection;

                    oCommand.CommandText = "INSERT INTO RIN3 (DocNum,WhsCode, ItemCode, ItemName, QtySrvd, UoM, LotNumber, IsFree, PriceListCode, ItemGuID) VALUES  (@DocNum, @WhsCode, @ItemCode, @ItemName, @QtySrvd, @UoM, @LotNumber, @IsFree, @PriceListCode, @ItemGuID)";

                    oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                    oCommand.Parameters.Add(new SqlParameter("@WhsCode", oUnit.Location));
                    oCommand.Parameters.Add(new SqlParameter("@ItemCode", oRow["ItemCode"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@ItemName", oRow["ItemName"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@UoM", oRow["UoM"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@QtySrvd", Convert.ToDouble(oRow["QtySrvd"].ToString())));
                    oCommand.Parameters.Add(new SqlParameter("@PriceListCode", oRow["PriceListCode"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@LotNumber", oRow["LotNumber"].ToString()));
                    if (oRow["IsFree"] != DBNull.Value)
                    {
                        oCommand.Parameters.Add(new SqlParameter("@IsFree", (bool)oRow["IsFree"]));
                    }
                    else
                    {
                        oCommand.Parameters.Add(new SqlParameter("@IsFree", false));
                    }
                    oCommand.Parameters.Add(new SqlParameter("@ItemGuID", oRow["ItemGuID"].ToString()));


                    oCommand.ExecuteNonQuery();
                }
            }




            #endregion

            #region Accounting Entries
            oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandType = CommandType.StoredProcedure;
            oCommand.CommandText = "SP_POST_SALESINVOICE_ENTRIES";
            oCommand.Parameters.Add(new SqlParameter("@DOCNUM", oUnit.DocumentNumber));

            oCommand.ExecuteNonQuery();
            #endregion

        }

        public void UpdateOrder(ProductReturnUnit oUnit)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "UPDATE ORIN SET DocStatus=@DocStatus, PostingDate=@PostingDate, DueDate=@DueDate, DocDate=@DocDate, Location=@Location, StoreCode=@StoreCode, CardCode=@CardCode, CardName=@CardName, ContactPerson=@ContactPerson, PaymentTerms=@PaymentTerms, Reference=@Reference, CustRefPO=@CustRefPO, BillTo=@BillTo, ShipTo=@ShipTo, SalesMan=@SalesMan, Remarks=@Remarks, GrossAmount=@GrossAmount, VATAmount=@VATAmount, WithHoldingTax=@WithHoldingTax, Discount1=@Discount1, Discount2=@Discount2, Discount3=@Discount3, Discount4=@Discount4, TotalDiscount=@TotalDiscount, NetAmount=@NetAmount, DivReference=@DivReference, Division=@Division, ModifiedBy=@ModifiedBy, DateModified=GetDate()" +
                                                        " WHERE DocNum=@DocNum";

            oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
            oCommand.Parameters.Add(new SqlParameter("@DocStatus", oUnit.DocumentStatus));
            oCommand.Parameters.Add(new SqlParameter("@PostingDate", oUnit.PostingDate));
            oCommand.Parameters.Add(new SqlParameter("@DueDate", oUnit.DueDate));
            oCommand.Parameters.Add(new SqlParameter("@DocDate", oUnit.DocDate));

            oCommand.Parameters.Add(new SqlParameter("@Location", oUnit.Location));
            oCommand.Parameters.Add(new SqlParameter("@StoreCode", oUnit.StoreCode));
            oCommand.Parameters.Add(new SqlParameter("@CardCode", oUnit.CardCode));
            oCommand.Parameters.Add(new SqlParameter("@CardName", oUnit.CardName));
            oCommand.Parameters.Add(new SqlParameter("@ContactPerson", oUnit.ContactPerson));
            oCommand.Parameters.Add(new SqlParameter("@PaymentTerms", oUnit.PaymentTerms));

            oCommand.Parameters.Add(new SqlParameter("@Reference", oUnit.Reference));
            oCommand.Parameters.Add(new SqlParameter("@CustRefPO", oUnit.CustRefPO));
            oCommand.Parameters.Add(new SqlParameter("@BillTo", oUnit.BillTo));
            oCommand.Parameters.Add(new SqlParameter("@ShipTo", oUnit.ShipTo));
            oCommand.Parameters.Add(new SqlParameter("@SalesMan", oUnit.SalesMan));
            oCommand.Parameters.Add(new SqlParameter("@Remarks", oUnit.Remarks));
            oCommand.Parameters.Add(new SqlParameter("@GrossAmount", oUnit.GrossAmnt));
            oCommand.Parameters.Add(new SqlParameter("@VATAmount", oUnit.VATAmt));
            oCommand.Parameters.Add(new SqlParameter("@WithHoldingTax", oUnit.WithHoldingTax));
            oCommand.Parameters.Add(new SqlParameter("@Discount1", oUnit.Discount1));
            oCommand.Parameters.Add(new SqlParameter("@Discount2", oUnit.Discount2));
            oCommand.Parameters.Add(new SqlParameter("@Discount3", oUnit.Discount3));
            oCommand.Parameters.Add(new SqlParameter("@Discount4", oUnit.Discount4));
            oCommand.Parameters.Add(new SqlParameter("@TotalDiscount", oUnit.TotalDiscount));
            oCommand.Parameters.Add(new SqlParameter("@NetAmount", oUnit.NetAmount));
            oCommand.Parameters.Add(new SqlParameter("@Division", oUnit.Division));
            oCommand.Parameters.Add(new SqlParameter("@DivReference", oUnit.DivReference));

            oCommand.Parameters.Add(new SqlParameter("@ModifiedBy", oUnit.ModifiedBy));

            oCommand.ExecuteNonQuery();

            oCommand = new SqlCommand();
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "DELETE RIN1 WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
            oCommand.ExecuteNonQuery();

            #region Line Items
            foreach (DataRow oRow in oUnit.LineItems.Rows)
            {

                if (oRow.RowState != DataRowState.Deleted)
                {
                    oCommand = new SqlCommand();
                    oCommand.Connection = this.Connection;

                    oCommand.CommandText = "INSERT INTO RIN1 (DocNum, ItemCode, ItemDesc, UoM, QtyOrder, CQtyOrder, Qty, CQty, PriceListCode, UnitPrice, IsFree, DiscPrcnt, Discount, TotalLineDisc, Amount, ItemGuid) VALUES " +
                                                                "(@DocNum, @ItemCode, @ItemDesc, @UoM, @QtyOrder, @CQtyOrder, @Qty, @CQty, @PriceListCode, @UnitPrice, @IsFree, @DiscPrcnt, @Discount, @TotalLineDisc, @Amount, @ItemGuid)";

                    oCommand.Parameters.Add(new SqlParameter("@ItemGuid", oRow["ItemGuid"]));
                    oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                    oCommand.Parameters.Add(new SqlParameter("@ItemCode", oRow["ItemCode"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@ItemDesc", oRow["ItemDesc"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@UoM", oRow["UoM"].ToString()));
                    if (oRow["Qty"] == DBNull.Value)
                    {
                        int _ZeroValue = 0;
                        oRow["Qty"] = _ZeroValue;
                    }
                    oCommand.Parameters.Add(new SqlParameter("@QtyOrder", Convert.ToDouble(oRow["QtyOrder"].ToString())));
                    oCommand.Parameters.Add(new SqlParameter("@CQtyOrder", Convert.ToDouble(oRow["CQtyOrder"].ToString())));
                    oCommand.Parameters.Add(new SqlParameter("@Qty", Convert.ToDouble(oRow["Qty"].ToString())));
                    oCommand.Parameters.Add(new SqlParameter("@CQty", Convert.ToDouble(oRow["CQty"].ToString())));
                    oCommand.Parameters.Add(new SqlParameter("@PriceListCode", oRow["PriceListCode"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@UnitPrice", Convert.ToDouble(oRow["UnitPrice"].ToString())));
                    if (oRow["IsFree"] != DBNull.Value)
                    {
                        oCommand.Parameters.Add(new SqlParameter("@IsFree", Convert.ToBoolean(oRow["IsFree"])));
                    }
                    else
                    {
                        oCommand.Parameters.Add(new SqlParameter("@IsFree", false));
                    }
                    oCommand.Parameters.Add(new SqlParameter("@DiscPrcnt", oRow["DiscPrcnt"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@Discount", Convert.ToDouble(oRow["Discount"].ToString())));
                    oCommand.Parameters.Add(new SqlParameter("@TotalLineDisc", oRow["TotalLineDisc"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@Amount", oRow["Amount"].ToString()));

                    oCommand.ExecuteNonQuery();

                }

            }

            #endregion

            oCommand = new SqlCommand();
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "DELETE RIN3 WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
            oCommand.ExecuteNonQuery();

            #region Inventory Issued Items
            foreach (DataRow oRow in oUnit.InventoryIssuedItems.Rows)
            {
                if (oRow.RowState != DataRowState.Deleted)
                {
                    oCommand = new SqlCommand();
                    oCommand.Connection = this.Connection;

                    oCommand.CommandText = "INSERT INTO RIN3 (DocNum,WhsCode, ItemCode, ItemName, QtySrvd, UoM, LotNumber, PriceListCode, ItemGuid) VALUES  (@DocNum, @WhsCode, @ItemCode, @ItemName, @QtySrvd, @UoM, @LotNumber, @PriceListCode, @ItemGuid)";
                    oCommand.Parameters.Add(new SqlParameter("@ItemGuid", oRow["ItemGuid"]));
                    oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                    oCommand.Parameters.Add(new SqlParameter("@WhsCode", oUnit.Location));
                    oCommand.Parameters.Add(new SqlParameter("@ItemCode", oRow["ItemCode"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@ItemName", oRow["ItemName"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@UoM", oRow["UoM"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@QtySrvd", Convert.ToDouble(oRow["QtySrvd"].ToString())));
                    oCommand.Parameters.Add(new SqlParameter("@LotNumber", oRow["LotNumber"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@PriceListCode", oRow["PriceListCode"].ToString()));


                    oCommand.ExecuteNonQuery();
                }
            }
            #endregion

            #region Updating Accounting Entries

            oCommand = new SqlCommand();
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "DELETE FROM DLN3 WHERE DOCNUM=@DOCNUM";
            oCommand.Parameters.Add(new SqlParameter("@DOCNUM", oUnit.DocumentNumber));
            oCommand.ExecuteNonQuery();


            oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandType = CommandType.StoredProcedure;
            oCommand.CommandText = "SP_POST_SALESINVOICE_ENTRIES";
            oCommand.Parameters.Add(new SqlParameter("@DOCNUM", oUnit.DocumentNumber));

            oCommand.ExecuteNonQuery();

            #endregion

        }
        #endregion

        #region Post/Cancel/Close Invoice
        public void PostDocument(string _DOCNUMBER, string PostedBy)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "UPDATE ORIN SET DOCSTATUS='Posted', DateModified=GetDate(), ModifiedBy=@ModifiedBy WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", _DOCNUMBER));
            oCommand.Parameters.Add(new SqlParameter("@ModifiedBy", PostedBy));
            oCommand.ExecuteNonQuery();


        }

        public void DeliverOrder(string _WAREHOUSE, string _SALES_ORDER_NO, string _DOCNUMBER, string DeliveredBy)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "UPDATE ORIN SET DOCSTATUS='Delivered', DateModified=GetDate(), ModifiedBy=@ModifiedBy WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", _DOCNUMBER));
            oCommand.Parameters.Add(new SqlParameter("@ModifiedBy", DeliveredBy));
            oCommand.ExecuteNonQuery();


            #region Applying Deduction to Inventory
            string selectSQL = "SELECT * FROM RIN3 WHERE DocNum=@DocNum";


            oCommand = new SqlCommand();
            oCommand.Connection = this.Connection;
            oCommand.CommandText = selectSQL;
            oCommand.Parameters.Add(new SqlParameter("@DocNum", _DOCNUMBER));
            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            foreach (DataRow oRow in ds.Tables[0].Rows)
            {
                StockManager oManager = new StockManager();
                oManager.Connection = this.Connection;

                if (oManager.IsLotNumberExists(_WAREHOUSE, oRow["ItemCode"].ToString(), oRow["LotNumber"].ToString()))
                {
                    oManager.UpdateStocks(_WAREHOUSE, oRow["ItemCode"].ToString(), oRow["LotNumber"].ToString(), (Convert.ToDouble(oRow["QtySrvd"]) * -1).ToString());
                }
                else
                {
                    WarehouseManager oWhs = new WarehouseManager();
                    oWhs.ConnectionString = this.ConnectionString;
                    oWhs.Open();
                    DataTable dt = oWhs.GetWarehouse(_WAREHOUSE);
                    oWhs.Close();
                    oManager.AddStocks(_WAREHOUSE, dt.Rows[0]["Dscription"].ToString(), oRow["ItemCode"].ToString(), oRow["ItemDesc"].ToString(), (Convert.ToDouble(oRow["Qty"]) * -1).ToString(), oRow["UoM"].ToString(), oRow["LotNumber"].ToString(), oRow["MfrDate"].ToString(), oRow["ExpiryDate"].ToString());
                }
            }
            #endregion

            #region Updating Sales Order Remarks

            if (_SALES_ORDER_NO.Trim() != "")
            {
                #region Getting the Line Items of SalesOrder
                OrderManager oSOManager = new OrderManager();
                oSOManager.Connection = this.Connection;
                DataTable dtSO = new DataTable();


                dtSO = oSOManager.GetOrderLineItems(_SALES_ORDER_NO);

                #endregion

                int iStatus = 2; // 1 = Open, 2 = Closed

                foreach (DataRow oRow in dtSO.Rows)
                {
                    oCommand = new SqlCommand();

                    oCommand.Connection = this.Connection;
                    oCommand.CommandText = "SELECT CASE WHEN SUM(Qty) IS NULL THEN 0 ELSE SUM(Qty) END Qty FROM ORIN JOIN RIN1 ON ORIN.DOCNUM = RIN1.DOCNUM WHERE RIN1.ItemGuid = @ItemGuid AND (ORIN.DOCSTATUS <> 'Draft' or ORIN.DocStatus <> 'Canceled')";

                    oCommand.Parameters.Add(new SqlParameter("@ItemGuid", oRow["ItemGuid"].ToString()));

                    DataTable dtRIN3 = new DataTable();
                    SqlDataAdapter dAdapter = new SqlDataAdapter();
                    dAdapter.SelectCommand = oCommand;
                    dAdapter.Fill(dtRIN3);

                    if (dtRIN3.Rows.Count > 0)
                    {
                        DataRow drRowRIN3 = dtRIN3.Rows[0];

                        if (Convert.ToDouble(oRow["Qty"]) != Convert.ToDouble(drRowRIN3["Qty"]))
                        {
                            iStatus = 1;
                            break;
                        }
                    }
                    else
                    {
                        iStatus = 1;
                        break;
                    }




                }

                if (iStatus == 2)
                {
                    oSOManager.CloseOrder(_SALES_ORDER_NO);
                }


                if (iStatus == 1)
                {
                    oSOManager.SetOrderOpen(_SALES_ORDER_NO);
                }


            }


            #endregion

        }

        public void CancelOrder(string sOrderNo, string CancelledBy)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "UPDATE ORIN SET DOCSTATUS='Canceled', DateModified=GetDate(), ModifiedBy=@ModifiedBy WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sOrderNo));
            oCommand.Parameters.Add(new SqlParameter("@ModifiedBy", CancelledBy));

            oCommand.ExecuteNonQuery();
        }

        public void CloseOrder(string _INVOICE_NO, string ClosedBy)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "UPDATE ORIN SET DOCSTATUS='Closed', DateModified=GetDate(), ModifiedBy=@ModifiedBy WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", _INVOICE_NO));
            oCommand.Parameters.Add(new SqlParameter("@ModifiedBy", ClosedBy));
            oCommand.ExecuteNonQuery();
        }

        public void SetOrderOpen(string sOrderNo)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "UPDATE ORIN SET DOCSTATUS='Open' WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sOrderNo));
            oCommand.ExecuteNonQuery();
        }

        #endregion

        #region Retreiving

        public DataTable GetDistribution(string sDocumentNumber)
        {
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM DLN3 WHERE DocNum = @DOCNUM";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable GetInvoices(string sDocumentNumber)
        {
            sDocumentNumber = '%' + sDocumentNumber.Trim() + '%';
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT TOP 500 *, NetAmount - AppliedAmount TotalBalance FROM ORIN WHERE DocNum LIKE @DOCNUM ORDER BY RecNo DESC";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable GetInvoicesBySIDRNo(string sDocumentNumber)
        {
            sDocumentNumber = '%' + sDocumentNumber.Trim() + '%';
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT TOP 500 *, NetAmount - AppliedAmount TotalBalance FROM ORIN WHERE DivReference LIKE @DOCNUM ORDER BY DivReference DESC";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable GetInvoicesByCustRefPO(string sDocumentNumber)
        {
            sDocumentNumber = '%' + sDocumentNumber.Trim() + '%';
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT TOP 500 *, NetAmount - AppliedAmount TotalBalance FROM ORIN WHERE CustRefPO LIKE @DOCNUM ORDER BY CustRefPO DESC";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable GetInvoicesByCustomer(string sCustomerName)
        {
            sCustomerName = sCustomerName.Trim() + '%';
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT TOP 500 *, NetAmount - AppliedAmount TotalBalance FROM ORIN WHERE CardName LIKE @DOCNUM ORDER BY CardName DESC";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sCustomerName));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];

        }

        public DataRow GetInvoice(string sDocumentNumber)
        {
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT *, NetAmount - AppliedAmount TotalBalance FROM ORIN WHERE DocNum = @DOCNUM";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            DataRow oRow = ds.Tables[0].Rows[0];

            return oRow;
        }

        public DataTable GetInvoiceLineItemForReturn(string sDocumentNumber)
        {
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM RIN1 WHERE DocNum = @DOCNUM";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);



            return ds.Tables[0];
        }


        public DataTable GetInvoiceLineItems(string sDocumentNumber)
        {
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM RIN1 WHERE DocNum = @DOCNUM";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);



            return ds.Tables[0];
        }


        public DataTable GetInvoiceLineItemsForReturn(string sDocumentNumber)
        {
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM VW_COPY_INVOICE_TO_RET WHERE DocNum = @DOCNUM";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);



            return ds.Tables[0];
        }


        public DataTable GetIssuedLineItems(string sDocumentNumber)
        {
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM RIN3 WHERE DocNum = @DOCNUM";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);



            return ds.Tables[0];
        }
        #endregion

        #region Report Retreivable

        public DataTable GetInvoiceStatus(DateTime _FROM, DateTime _TO)
        {
            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandType = CommandType.StoredProcedure;
            oCommand.CommandText = "SP_DR_STATUS";
            oCommand.Parameters.Add(new SqlParameter("@FROM", _FROM));
            oCommand.Parameters.Add(new SqlParameter("@TO", _TO));

            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        #endregion
    }
}
